﻿using System;
using SmartSiteManagementSystem.IRepository.User;
using SmartSiteManagementSystem.Models.User;
using SmartSiteManagementSystem.Repository.DBHelper;
using System.Data;
using Dapper;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using MySql.Data.MySqlClient;

namespace SmartSiteManagementSystem.Repository.User
{
    public class UserInfoRepository : IUserInfoRepository
    //,IDisposable
    {
        #region dataTable
        /*
create table `user`( 
	`user_id` int(11) not null AUTO_INCREMENT primary key, 
    `position_type` smallint NOT NULL,
	`logon_name` nvarchar(60) NOT NULL,
	`show_name` varchar(100) NULL,
	`email` char(100) NOT NULL,
	`mobile` char(100) NOT NULL,
	`reg_ip` varchar(50) NOT NULL,
	`logon_pwd` varchar(50) NOT NULL,
	`create_time` datetime NOT NULL default now(),
	`lastest_logonip` varchar(50) NOT NULL,
	`lastest_logontime` datetime NOT NULL default now(),
	`login_count` int NOT NULL,
	`is_stop` bit NOT NULL default 0,
	`is_delete` bit NOT NULL default 0,
	`sex` smallint NOT NULL,
	`reg_type` int NOT NULL,
	`head_pic` varchar(200) NULL
) default charset=utf8;
         */
        #endregion
       // private IDbConnection _readConn;
       // private IDbConnection _writeConn;

        public bool Delete(Hashtable param)
        {
            bool back = false;
            if (param == null)
                param = new Hashtable();
            String querySql = @"delete from user";
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where ");
                    int parCount = 0;
                    if (param.ContainsKey("user_id"))
                    {
                        pars.Add("user_id", param["user_id"]);

                        whereCondition.Append(" user_id =@user_id");
                        parCount++;
                    }
                }
                string execSql = querySql + whereCondition.ToString();
                int backInt = _writeConn.Execute(execSql, pars);
                if (backInt > 0)
                {
                    back = true;
                }
            }
            return back;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public UserInfo Get(Hashtable param)
        {
            UserInfo back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"SELECT user_id as UserId,position_type as PositionType,logon_name as LogonName,show_name as ShowName,email as Email,mobile as Mobile,reg_ip as RegIp,logon_pwd as LogonPwd,create_time as CreateTime,lastest_logonip as LastestLogonIp,lastest_logontime as LastestLogonTime, login_count as LoginCount, is_stop as IsStop,is_delete as IsDelete,sex as Sex,reg_type as RegType,head_pic as HeadPic FROM user";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {

                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("user_id"))
                    {
                        pars.Add("user_id", param["user_id"]);
                        whereCondition.Append(" and user_id =@user_id");
                    }
                    if (param.ContainsKey("position_type"))
                    {
                        pars.Add("position_type", param["position_type"]);
                        whereCondition.Append(" and position_type =@position_type");
                    }

                    if (param.ContainsKey("logon_pwd"))
                    {
                        pars.Add("logon_pwd", param["logon_pwd"]);
                        whereCondition.Append(" and logon_pwd =@logon_pwd");
                    }

                    if (param.ContainsKey("reg_type"))
                    {
                        pars.Add("reg_type", param["reg_type"]);
                        whereCondition.Append(" and reg_type =@reg_type");
                    }

                    if (param.ContainsKey("logon_name"))
                    {
                        pars.Add("logon_name", param["logon_name"]);
                        whereCondition.Append(" and logon_name =@logon_name");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if (param.ContainsKey("is_delete"))
                    {
                        pars.Add("is_delete", param["is_delete"]);
                        whereCondition.Append(" and is_delete =@is_delete");
                    }
                }
                whereCondition.Append(" limit 1");
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.QueryFirstOrDefault<UserInfo>(execSql, pars);
            }
            return back;
        }

        public int GetCount(Hashtable param)
        {
            int back = 0;
            if (param == null)
                param = new Hashtable();
            String querySql = "select count(user_id) from user";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("user_id"))
                    {
                        pars.Add("user_id", param["user_id"]);
                        whereCondition.Append(" and user_id =@user_id");
                    }
                    if (param.ContainsKey("position_type"))
                    {
                        pars.Add("position_type", param["position_type"]);
                        whereCondition.Append(" and position_type =@position_type");
                    }

                    if (param.ContainsKey("logon_pwd"))
                    {
                        pars.Add("logon_pwd", param["logon_pwd"]);
                        whereCondition.Append(" and logon_pwd =@logon_pwd");
                    }

                    if (param.ContainsKey("reg_type"))
                    {
                        pars.Add("reg_type", param["reg_type"]);
                        whereCondition.Append(" and reg_type =@reg_type");
                    }

                    if (param.ContainsKey("logon_name"))
                    {
                        pars.Add("logon_name", param["logon_name"]);
                        whereCondition.Append(" and logon_name =@logon_name");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if (param.ContainsKey("is_delete"))
                    {
                        pars.Add("is_delete", param["is_delete"]);
                        whereCondition.Append(" and is_delete =@is_delete");
                    }
                }

                string execSql = querySql + whereCondition.ToString();
                #endregion
                back = _readConn.QueryFirstOrDefault<int>(execSql, pars);
            }
            return back;
        }

        public IList<UserInfo> GetList(Hashtable param, string orderBy, int first, int rows)
        {

            IList<UserInfo> back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"SELECT user_id as UserId,position_type as PositionType,logon_name as LogonName,show_name as ShowName,email as Email,mobile as Mobile,reg_ip as RegIp,logon_pwd as LogonPwd,create_time as CreateTime,lastest_logonip as LastestLogonIp,lastest_logontime as LastestLogonTime, login_count as LoginCount, is_stop as IsStop,is_delete as IsDelete,sex as Sex,reg_type as RegType,head_pic as HeadPic FROM user";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("user_id"))
                    {
                        pars.Add("user_id", param["user_id"]);
                        whereCondition.Append(" and user_id =@user_id");
                    }
                    if (param.ContainsKey("position_type"))
                    {
                        pars.Add("position_type", param["position_type"]);
                        whereCondition.Append(" and position_type =@position_type");
                    }

                    if (param.ContainsKey("logon_pwd"))
                    {
                        pars.Add("logon_pwd", param["logon_pwd"]);
                        whereCondition.Append(" and logon_pwd =@logon_pwd");
                    }

                    if (param.ContainsKey("reg_type"))
                    {
                        pars.Add("reg_type", param["reg_type"]);
                        whereCondition.Append(" and reg_type =@reg_type");
                    }

                    if (param.ContainsKey("logon_name"))
                    {
                        pars.Add("logon_name", param["logon_name"]);
                        whereCondition.Append(" and logon_name =@logon_name");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if (param.ContainsKey("is_delete"))
                    {
                        pars.Add("is_delete", param["is_delete"]);
                        whereCondition.Append(" and is_delete =@is_delete");
                    }
                }
                if (string.IsNullOrEmpty(orderBy))
                {
                    whereCondition.Append(" order by user_id desc");
                }
                else
                {
                    whereCondition.Append(" order by @orderBy");
                    pars.Add("orderBy", orderBy);
                }
                whereCondition.Append(" limit @first,@rows");
                pars.Add("first", first);
                pars.Add("rows", rows);
                #endregion
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.Query<UserInfo>(execSql, pars).AsList<UserInfo>();
            }
            return back;
        }

        public int Insert(UserInfo obj)
        {
            int back = 0;
            #region sql
            string sql = @"INSERT INTO user
(
position_type,
logon_name,
show_name,
email,
mobile,
reg_ip,
logon_pwd,
create_time,
lastest_logonip,
lastest_logontime,
login_count,
is_stop,
is_delete,
sex,
reg_type,
head_pic
)
VALUES
(
@position_type,
@logon_name,
@show_name,
@email,
@mobile,
@reg_ip,
@logon_pwd,
@create_time,
@lastest_logonip,
@lastest_logontime,
@login_count,
@is_stop,
@is_delete,
@sex,
@reg_type,
@head_pic);select last_insert_id();";
            #endregion
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                back = _writeConn.QueryFirst<int>(sql, new
                {
                    position_type=obj.PositionType,
                    logon_name=obj.LogonName,
                    show_name= obj.ShowName,
                    email= obj.Email,
                    mobile = obj.Mobile,
                    reg_ip = obj.RegIp,
                    logon_pwd = obj.LogonPwd,
                    create_time = obj.CreateTime,
                    lastest_logonip = obj.LastestLogonIp,
                    lastest_logontime = obj.LastestLogonTime,
                    login_count = obj.LoginCount,
                    is_stop = obj.IsStop,
                    is_delete = obj.IsDelete,
                    sex = obj.Sex,
                    reg_type = obj.RegType,
                    head_pic = obj.HeadPic
                });
            }
            return back;
        }

        public bool Update(UserInfo obj)
        {
            bool back = false;
            #region sql
            string sql = @"update user
set
logon_name = @logon_name,
show_name =@show_name ,
email = @email,
mobile =@mobile ,
reg_ip =@reg_ip,
logon_pwd =@logon_pwd,
create_time =@create_time ,
lastest_logonip = @lastest_logonip,
lastest_logontime =@lastest_logontime,
login_count =@login_count,
is_stop = @is_stop,
is_delete = @is_delete,
sex = @sex ,
reg_type = @reg_type,
head_pic =@head_pic
WHERE user_id =@user_id;";
            #endregion
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                int backint = _writeConn.Execute(sql, new
                {
                    user_id = obj.UserId,
                    position_type = obj.PositionType,
                    logon_name = obj.LogonName,
                    show_name = obj.ShowName,
                    email = obj.Email,
                    mobile = obj.Mobile,
                    reg_ip = obj.RegIp,
                    logon_pwd = obj.LogonPwd,
                    create_time = obj.CreateTime,
                    lastest_logonip = obj.LastestLogonIp,
                    lastest_logontime = obj.LastestLogonTime,
                    login_count = obj.LoginCount,
                    is_stop = obj.IsStop,
                    is_delete = obj.IsDelete,
                    sex = obj.Sex,
                    reg_type = obj.RegType,
                    head_pic = obj.HeadPic
                });
                if (backint > 0)
                {
                    back = true;
                }
            }
            return back;
        }
    }
}

